USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[SyozokutiSyukei] スクリプト日付: 2014/05/14 17:28:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SyozokutiSyukei]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SyozokutiSyukei];
GO

CREATE PROCEDURE [dbo].[SyozokutiSyukei]
	@TAIKAINO int,
	@TAIKOSENNO int
AS
	SELECT
		A.TAIKOSENNO,
		A.HOUHOU,
		C.SYOZOKUTINO,
		C.SYOZOKUTIMEI,
		B.SYUKEINO,
		B.JYUNI,
		B.TOKUTEN, 
		B.BONUS,
		B.SANKA,
		C.PRINTNO 
	FROM 
		TBL_TAIKOU AS A 
	LEFT JOIN 
		TBL_TAIKOU_KEKKA AS B 
	ON 
		A.TAIKAINO = B.TAIKAINO 
		AND A.TAIKOSENNO = B.TAIKOSENNO 
	LEFT JOIN 
		TBL_SYOZOKUTI AS C 
	ON 
		B.SYOZOKU = C.SYOZOKUTINO 
	WHERE 
		A.TAIKAINO = @TAIKAINO 
		AND A.TAIKOSENNO = @TAIKOSENNO 
		ORDER BY 
		B.JYUNI,
		B.TOKUTEN DESC,
		C.PRINTNO
